import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


import org.jsoup.Jsoup;
import org.jsoup.Connection.Response;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;




public class EbayCrawler {
	Connection connection;
	ExecutorService executor;
	public EbayCrawler() throws SQLException, IOException {
		connection= DB.getConnectionRead();
		connection.setAutoCommit(false);
		File dir = new File("images/");
		if (!dir.exists()) {
				dir.mkdir();
		
		}
		executor = Executors.newFixedThreadPool(5);
	}
	public static void main(String[] args) throws SQLException, IOException, ParseException {
		//String url="http://www.ebay.com/csc/eforcity/m.html?LH_Complete=1&_trkparms=65%253A13%257C66%253A4%257C39%253A1&rt=nc&_dmd=1&_ipg=200&_since=15&_sop=13&_trksid=p3911.c0.m14.l1513";
		EbayCrawler crawler = new EbayCrawler();
		
		crawler.run();		

	}
	private void downloadImage(String url, String listingId)  {
	try{
		
		String ext = url.substring(url.lastIndexOf("."));
		
			
		org.jsoup.Connection connectionJsoup = Jsoup.connect(url).timeout(60000).ignoreContentType(true).userAgent("Mozilla/5.0 (Windows NT 5.1; rv:2.0.1) Gecko/20100101 Firefox/4.0.1");
		Response response= connectionJsoup.execute();
		byte[] rs= response.bodyAsBytes();
		
		File file = new File("images/"+listingId+ext);
	
		FileOutputStream fos = new FileOutputStream(file);
		fos.write(rs);
	fos.close();
	Thread.sleep(500);}catch (Exception e) {
	//	e.printStackTrace();
	}}
	
  	private List<EbayURL> getURLs() throws SQLException{
  		List<EbayURL> urls= new ArrayList<EbayURL>();
		Statement statement = connection.createStatement();
		ResultSet rs= statement.executeQuery("select id,storeName,storeURL from ebayURLS");
		while(rs.next()){
			EbayURL eUrl = new EbayURL();
			eUrl.setId(rs.getInt(1));
			eUrl.setStoreName(rs.getString(2));
			eUrl.setUrl(rs.getString(3));
			urls.add(eUrl);
			
		}
  		return urls;
  		
  	}
  	public void run() throws SQLException, IOException, ParseException{
  		List<EbayURL> urls = getURLs();
  		for (EbayURL ebayURL : urls) {
  			System.out.println(new Date()+" - Start crawling data from "+ebayURL.getUrl());
			crawl(ebayURL.getUrl());
  			System.out.println(new Date()+" - Finish crawling data from "+ebayURL.getUrl());

		}
  		executor.shutdown();
  		System.out.println("images downloading...");

  		while (!executor.isTerminated()) {

		}
		System.out.println("Finished all download threads");
  			
  	}
  	
  	
  	private void crawl(String url) throws IOException, ParseException{
  		int _pgn=1;
  		boolean loop=true;
  		int count=0;
  		String storeId="";
  		int page_count=0;
  		int rsperpage=0;
  		List<String> id_lists = new ArrayList<String>();
  		Map<String, String> cookies=new HashMap<String, String>();
  		do {
  		//	System.err.println("page number:"+_pgn);
  			org.jsoup.Connection jConnection=Jsoup.connect(url+"&_pgn="+_pgn).timeout(100000).userAgent("Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20100101 Firefox/8.0") ;
  		Document doc ;
  		
  		if(_pgn==1){
  			
  			doc = jConnection.get();
  		 cookies=	jConnection.response().cookies();
  	//	 System.err.println(cookies.size());
  			  		NumberFormat nf = NumberFormat.getInstance(Locale.US);
  		count= nf.parse(doc.select("span.countClass").text().trim()).intValue();
  		//System.err.println(count);
  		storeId= doc.select("span.mbg-nw").text().trim();
  		String pageStr= doc.select("li.toppg-t").text().trim();
  		if(pageStr.startsWith("Page")) page_count= Integer.parseInt(pageStr.substring(pageStr.lastIndexOf(" ")+1).trim());
  //	System.err.println(page_count);
  	rsperpage=count/page_count;
  	if(count%page_count!=0) rsperpage=rsperpage+1;
  //	System.err.println(rsperpage);
  		}else {
  			for(Map.Entry<String, String> cookie: cookies.entrySet()){
  				jConnection =jConnection.cookie(cookie.getKey(), cookie.getValue());
  			//	System.err.println(cookie.getKey()+":"+ cookie.getValue());
  			}
  			doc = jConnection.get();
  	//		System.err.println(doc.select("li.toppg-t").text().trim());
  		}
  		Element results= doc.getElementById("ResultSet");
  		if(results!=null){
  			Elements tbls= results.select("table.li[cellspacing=0]");
  		//	System.err.println(tbls.size());
  			List<EbayCompletedListing> listings = new ArrayList<EbayCompletedListing>();
  			int i=0;
  			for (Element tb : tbls) {
  				//System.err.println(i++);
  				EbayCompletedListing completedListing=	 	getCompletedListing(tb,storeId);
  				if(completedListing!=null){
  				if(!id_lists.contains(String.valueOf(completedListing.getListingId()))){
  					id_lists.add(String.valueOf(completedListing.getListingId()));
  					
  					listings.add(completedListing);
  		  			

  					
  				}else{
  					
  					//System.err.println("duplicate listingId: "+ completedListing.getListingId());
  				}	
  				
			}}
  	//	System.err.println("start insert");
  			insert(listings);
  			System.out.println(new Date()+" - Number of data insert: " +listings.size());
  		}
  		_pgn++;
  		  	}while((_pgn<=page_count&&page_count<10000/rsperpage)||_pgn<=10000/rsperpage);
  		}
  	
  	private void insert(List<EbayCompletedListing> listings){
  		try {
			PreparedStatement ps = connection
					.prepareStatement("insert into ebayCompletedListings(storeId,listingId,title,auctionPrice,buyItNowPrice,shipPrice,sold,listingDate) values(?,?,?,?,?,?,?,?)");
			for (EbayCompletedListing ebay : listings) {
				ps.setString(1, ebay.getStoreId());
				ps.setLong(2, ebay.getListingId());
				ps.setString(3, ebay.getTitle());
				ps.setDouble(4, ebay.getAuctionPrice());
				ps.setDouble(5, ebay.getBuyItNowPrice());
				ps.setDouble(6, ebay.getShipPrice());
				ps.setShort(7, ebay.getSold());
				ps.setTimestamp(8, new Timestamp(ebay.getListingDate().getTime()));
				ps.addBatch();
			}
		ps.executeBatch() 	;
			connection.commit();
  			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

  		
  		
  	}
  	
  	private boolean checkListingId(long listingId) throws SQLException{
  		
  		PreparedStatement ps = connection
				.prepareStatement("select listingId from ebayCompletedListings where listingId=?");
  		ps.setLong(1, listingId);
  		ResultSet rs= ps.executeQuery();
  		if (rs.getRow()>0) return true;
  		
  		return false;
  	}
  	private EbayCompletedListing getCompletedListing(Element tb,String storeId){
  		EbayCompletedListing completedListing = new EbayCompletedListing();
  		try {
  			if(tb.select("span.sold").text().equalsIgnoreCase("Sold")) completedListing.setSold((short) 1);
  			else{
  				return null;}
  			
  			completedListing.setStoreId(storeId);
  			Element a =tb.select("div.ttl a.vip").first();
			String title = a.attr("title");
			completedListing.setTitle(title);
			String href = a.attr("href");
		    final	String listingId = href.substring(href.lastIndexOf("/")+1,href.lastIndexOf("?"));
		    if(!checkListingId(Long.parseLong(listingId))){
		//\	System.err.println(listingId);
			completedListing.setListingId(Long.parseLong(listingId));
			Element prc = tb.select("td.prc").first();
			String priceBidStr= prc.getElementsByAttributeValue("class", "binsold g-b").text().trim();
			if(priceBidStr.startsWith("$")) completedListing.setAuctionPrice(Double.parseDouble(priceBidStr.substring(1)));
			String priceBuyStr=prc.getElementsByAttributeValue("class", "binsold").text().trim();
			if(priceBuyStr.startsWith("$")) completedListing.setBuyItNowPrice(Double.parseDouble(priceBuyStr.substring(1)));
			String priceShipStr=prc.select("div.tfsp").text().trim();
			if(priceShipStr.startsWith("$")) completedListing.setShipPrice(Double.parseDouble(priceShipStr.substring(1)));
					
		     final	String imageUrl= tb.select("img.img").attr("src");
			
		  Thread downloadThread=	new Thread(){public void run() {downloadImage(imageUrl, listingId);};};
		  executor.execute(downloadThread);}
  		} catch (Exception e) {
			completedListing=null;
			System.err.println(e);// TODO: handle exception
		}
  		
  		return completedListing;
  	}
}
